package com.wcpdoc.exam.report.dao.impl;

import com.wcpdoc.core.dao.impl.RBaseDaoImpl;
import com.wcpdoc.core.entity.PageIn;
import com.wcpdoc.core.entity.PageOut;
import com.wcpdoc.core.util.DateUtil;
import com.wcpdoc.core.util.HibernateUtil;
import com.wcpdoc.core.util.SqlUtil;
import com.wcpdoc.core.util.SqlUtil.Order;
import com.wcpdoc.exam.report.dao.MockReportDao;
import com.wcpdoc.exam.report.dao.ReportDao;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * 统计数据访问层实现
 * <p>
 * v1.0 chenyun 2021-03-24 13:39:37
 */
@Repository
public class MockReportDaoImpl extends RBaseDaoImpl<Object> implements MockReportDao {
    private static final Logger log = LoggerFactory.getLogger(MockReportDaoImpl.class);

    @Override
    public PageOut getListpage(PageIn pageIn) {
        return null;
    }

    @Override
    public List<Map<String, Object>> getListUser(String id) {
        return null;
    }


    @Override
    public PageOut mockExamRankListpage(PageIn pageIn) {
        String sql = "SELECT MY_MOCK_EXAM.NO AS MY_MOCK_EXAM_NO, " // 排名
                + "USER.ID AS USER_ID, USER.NAME AS USER_NAME, ORG.NAME AS ORG_NAME,USER.LOGIN_NAME AS LOGIN_NAME,LABEL.NAME AS LABEL_NAME,"
                + "case when USER.SEX ='1' then '男' when USER.SEX ='2' then '女' else '未知' end as SEX  , "// 用户机构信息
                + "MY_MOCK_EXAM.STATE AS MY_MOCK_EXAM_STATE, MY_MOCK_EXAM.MARK_STATE AS MY_MOCK_EXAM_MARK_STATE, MY_MOCK_EXAM.ANSWER_STATE AS MY_MOCK_EXAM_ANSWER_STATE, "// 考试状态信息
                + "MY_MOCK_EXAM.ANSWER_START_TIME AS MY_MOCK_EXAM_START_TIME, MY_MOCK_EXAM.ANSWER_END_TIME AS MY_MOCK_EXAM_END_TIME, "// 答题时间
                + "MY_MOCK_EXAM.MARK_START_TIME AS MY_MOCK_EXAM_MARK_START_TIME, MY_MOCK_EXAM.MARK_END_TIME AS MY_MOCK_EXAM_MARK_END_TIME, "//阅卷时间
                + "MY_MOCK_EXAM.TOTAL_SCORE AS MY_MOCK_EXAM_TOTAL_SCORE, "// 用户分数
                + "EXAM.MARK_TYPE AS EXAM_MARK_TYPE, EXAM.MARK_STATE AS EXAM_MARK_STATE "// 考试信息，用于判断如果是主观题试卷，分数应该考试结束才显示
                + "FROM EXM_MY_MOCK_EXAM MY_MOCK_EXAM "// 我的考试
                + "INNER JOIN EXM_MOCK_EXAM EXAM ON MY_MOCK_EXAM.EXAM_ID = EXAM.ID "// 考试
                + "INNER JOIN SYS_USER USER ON MY_MOCK_EXAM.USER_ID = USER.ID "// 考试用户
                + "LEFT JOIN SYS_USER MARK_USER ON MY_MOCK_EXAM.MARK_USER_ID = MARK_USER.ID "// 阅卷用户
                + "LEFT JOIN SYS_ORG ORG ON USER.ORG_ID = ORG.ID " // 机构名称
                + "LEFT JOIN SYS_LABEL LABEL ON USER.LABEL_ID = LABEL.ID " // 标签名称
                ;
        SqlUtil sqlUtil = new SqlUtil(sql)
                .addWhere("MY_MOCK_EXAM.EXAM_ID = :EXAM_ID", pageIn.get("examId"))
                .addOrder("MY_MOCK_EXAM.NO", Order.ASC)
                .addOrder("MY_MOCK_EXAM.ANSWER_END_TIME", Order.DESC);
        log.info("=====查询排名");
        PageOut pageOut = getListpage(sqlUtil, pageIn);
        HibernateUtil.formatDate(pageOut.getList(),
                "myMockExamStartTime", DateUtil.FORMAT_DATE_TIME,
                "myMockExamEndTime", DateUtil.FORMAT_DATE_TIME,
                "myMockExamMarkStartTime", DateUtil.FORMAT_DATE_TIME,
                "myMockExamMarkEndTime", DateUtil.FORMAT_DATE_TIME);
        return pageOut;
    }

    @Override
    public List<Map<String, Object>> mockExportFile(Integer examId) {
        String sql = "SELECT MY_MOCK_EXAM.NO AS MY_MOCK_EXAM_NO, " // 排名
                + "USER.ID AS USER_ID, USER.NAME AS USER_NAME, ORG.NAME AS ORG_NAME,"// 用户机构信息

                /*2024年1月3日 增加导出用户信息*/
                + "USER.LOGIN_NAME AS LOGIN_NAME,LABEL.NAME AS LABEL_NAME,"
                + "case when USER.SEX ='1' then '男' when USER.SEX ='2' then '女' else '未知' end as SEX," +
                /*end 增加导出用户信息*/
                " case " +
                "	when MY_MOCK_EXAM.STATE =1 then '未考试' " +
                "	when MY_MOCK_EXAM.STATE =2 then '考试中'  " +
                "	when MY_MOCK_EXAM.STATE =3 then '已交卷' else '未知' end AS MY_MOCK_EXAM_STATE,"


                + "MY_MOCK_EXAM.ANSWER_STATE AS MY_MOCK_EXAM_ANSWER_STATE, "// 考试状态信息
                + "MY_MOCK_EXAM.ANSWER_START_TIME AS MY_MOCK_EXAM_START_TIME, MY_MOCK_EXAM.ANSWER_END_TIME AS MY_MOCK_EXAM_END_TIME, "// 答题时间

                + "MY_MOCK_EXAM.TOTAL_SCORE AS MY_MOCK_EXAM_TOTAL_SCORE, "// 用户分数
                + "EXAM.MARK_TYPE AS EXAM_MARK_TYPE, EXAM.MARK_STATE AS EXAM_MARK_STATE "// 考试信息，用于判断如果是主观题试卷，分数应该考试结束才显示
                + "FROM EXM_MY_MOCK_EXAM MY_MOCK_EXAM "// 我的考试

                + "INNER JOIN EXM_MOCK_EXAM EXAM ON MY_MOCK_EXAM.EXAM_ID = EXAM.ID "// 考试

                + "INNER JOIN SYS_USER USER ON MY_MOCK_EXAM.USER_ID = USER.ID "// 考试用户

                + "LEFT JOIN SYS_LABEL LABEL ON USER.LABEL_ID = LABEL.ID "// 标签

                + "LEFT JOIN SYS_ORG ORG ON USER.ORG_ID = ORG.ID " +
                " where MY_MOCK_EXAM.EXAM_ID ='" + examId + "' ";// 机构名称
        log.info("=====查询排名");
        List<Map<String, Object>> mapList = getMapList(sql);
        return mapList;
    }

    @Override
    public List<Map<String, Object>> mockQuestionErrList(Integer examId) {
        String sql = "SELECT QUESTION.ID AS QUESTION_ID, QUESTION.TITLE AS QUESTION_TITLE, "
                + "	MY_QUESTION.USER_NUM, MY_QUESTION.SUCC_USER_NUM "
                + "FROM (SELECT QUESTION_ID, COUNT(USER_ID) AS USER_NUM, SUM( SCORE = USER_SCORE ) AS SUCC_USER_NUM "
                + "			FROM EXM_MY_QUESTION "
                + "			WHERE EXAM_ID = :EXAM_ID AND TYPE = 2 "
                + "			GROUP BY QUESTION_ID ) MY_QUESTION "
                + "INNER JOIN EXM_QUESTION QUESTION ON MY_QUESTION.QUESTION_ID = QUESTION.ID "
                + "ORDER BY MY_QUESTION.SUCC_USER_NUM DESC";
        return getMapList(sql, new Object[]{examId});
    }

    @Override
    public List<Map<String, Object>> count(Integer examId) {
        String sql = "SELECT EXM_EXAM.TOTAL_SCORE AS EXAM_TOTAL_SCORE, EXM_EXAM.PASS_SCORE AS EXAM_PASS_SCORE, EXM_EXAM.START_TIME AS EXAM_START_TIME, "
                + "EXM_EXAM.END_TIME AS EXAM_END_TIME, MAX( MY_Mock_EXAM.TOTAL_SCORE ) AS MAX, MIN( MY_Mock_EXAM.TOTAL_SCORE ) AS MIN, AVG( MY_Mock_EXAM.TOTAL_SCORE ) AS AVG, "
                + "SUM( MY_Mock_EXAM.ANSWER_STATE = 1 ) AS EXAM_USER_ANSWER, COUNT( MY_Mock_EXAM.ID ) AS EXAM_USER_SUM, MAX( MY_Mock_EXAM.ANSWER_END_TIME ) AS MAX_EXAM, "
                + "MIN( MY_Mock_EXAM.ANSWER_END_TIME ) AS MIN_EXAM FROM EXM_MY_Mock_EXAM MY_Mock_EXAM "
                + "INNER JOIN EXM_EXAM ON EXM_EXAM.ID = MY_Mock_EXAM.EXAM_ID "
                + "WHERE MY_Mock_EXAM.EXAM_ID = :EXAM_ID";
        return getMapList(sql, new Object[]{examId});
    }

}
